package comm.hg.db;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class MySOL {
    //ComboPooledDataSource   c3p0数据源（表示是一个连接池）
    private static Connection dataSource = null;

    /**
     * 获取连接池对象
     */
    public static Connection getDataSource() {
        if(dataSource==null){

            //加载驱动
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url="com.jdbc:mysql://127.0.0.1:3306/shouyin?characterEncoding=UTF-8";
                String user="root";
                String password="123456";

                //创建数据连接对象
                dataSource= DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("数据库连接异常");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("数据库连接异常");
            }
        }

        return dataSource;
    }

    /**
     * 查询一条数据
     * @param id
     * @throws SQLException
     */
    public static void queryByid(String id) throws SQLException {
        //创建QueryRunner对象
        QueryRunner qr=new QueryRunner();
        //执行查询
        String sql="select * from a where id=?";
        Student c=qr.query(getDataSource(),sql,new BeanHandler<Student>(Student.class),id);
        System.out.println(c.getNameAjj());
    }

    /**
     * 查询所有信息
     * @throws SQLException
     */
    public static void queryList() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from student";
        List<Student> c = qr.query(getDataSource(),sql, new BeanListHandler<Student>(Student.class));
        for (Student category : c) {
            System.out.println(category);
        }
    }

    /**
     * 查询所有信息  带条件
     * @throws SQLException
     */
    public static void queryList(String name) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from category where name=?";
        List<Student> c = qr.query(getDataSource(),sql, new BeanListHandler<Student>(Student.class),name);
        for (Student category : c) {
            System.out.println(category);
        }
    }

    /**
     * 返回行数
     *
     * (page-1)*size
     *
     * @throws SQLException
     */
    public static void queryCount() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select count(*) from category";
        Long count = qr.query(getDataSource(),sql, new ScalarHandler<Long>());
        // 3.List
        System.out.println(count);
    }

    /**
     * 增加
     * @throws SQLException
     */
    public static void updateOrDeleeOrInsert(String id,String name) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "insert into student values(?,?)";
        int number = qr.update(getDataSource(),sql, id,name);
        // 3.List
        System.out.println(number);
    }

    /**
     * 删除、修改
     * @throws SQLException
     */
    public static void updateOrDeleeOrInsert(String id) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "delete from student where id=?";
        int number = qr.update(getDataSource(),sql, id);
        // 3.List
        System.out.println(number);
    }


    public static void main(String[] args) {
        String a="2";
        try {
            queryByid(a);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

